﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_PostDocument_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_PostDocument_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_PostDocument_V2]
    @StaffName nvarchar(300),   -- The name of the user who posted the document
    @DocID int,                 -- The id of the document
    @ProjectID int = 0
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

IF ( @@TRANCOUNT = 0 )
BEGIN
    RAISERROR(N'This sproc must be executed within an active transaction.', 16, 1);
    RETURN -1;
END

DECLARE @FlowRule int
DECLARE @Position_ID int
DECLARE @Super_Position_ID int

DECLARE @Project_ID INT
DECLARE @Parent_Project_ID int
DECLARE @PassNum int
DECLARE @StaffID int

DECLARE @Flow_ID int
DECLARE @Step_ID int

DECLARE @Local_Alert int
DECLARE @msgid int

SET @Project_ID     = @ProjectID
SET @PassNum         = 0
SET @Local_Alert    = 0


--=======================================================================
--得到人员职位ID
SELECT
    @StaffID = s.Staff_ID,
    @Position_ID = p.Position_ID
FROM
    dbo.UDS_Staff s,
    dbo.UDS_Staff_In_Position p
WHERE
    s.Staff_ID = p.Staff_ID
    and s.staff_name = @StaffName;
IF ( @@rowcount <> 1 )
begin
    raiserror(N'The specified staff "%s" cannot be found.', 16, 1, @StaffName);
    return -1;
end;


--得到上级职位ID
SELECT @Super_Position_ID = Super_Position_ID FROM dbo.UDS_Position WHERE Position_id = @Position_ID;

--得到上级项目ID
SELECT @Parent_Project_ID = ClassParentID FROM dbo.UDS_Class WHERE ClassID = @Project_ID and ClassID <> ClassParentID;

--得到文档的当前流程ID和步骤ID
SELECT @Flow_ID = Flow_ID, @Step_ID = Step_ID FROM dbo.UDS_Flow_Document WHERE Doc_ID = @DocID;


--=======================================================================
--该步骤是否存在
IF NOT EXISTS(SELECT 1 FROM dbo.UDS_Flow_Step WHERE Flow_ID = @Flow_ID and Step_ID = @Step_ID)
BEGIN
    RAISERROR('The flow step used by the document does not exist', 16, 1);
    RETURN -1;
END

--得到当前环节流转规则和会签规则
SELECT @FlowRule = Flow_Rule, @PassNum = PassNum, @Local_Alert = LocalAlert
    FROM dbo.UDS_Flow_Step
    WHERE Flow_ID = @Flow_ID AND Step_ID     = @Step_ID

--汇签规则
IF @PassNum < 0  -- 需全体通过
BEGIN
    --删除真正的消息
    DELETE FROM dbo.UDS_SMS_MobileMsgSendBuffer        
        WHERE     msgid in (
                    SELECT  msgid 
                        FROM 
                            dbo.UDS_Flow_Message
                        WHERE
                            doc_id          = @docid
                            and Flow_ID     = @Flow_ID
                            and Step_ID     = @Step_ID            
                            and staff_id    = @staffid        
                  )

    --删除当前步骤的提醒
    DELETE FROM dbo.uds_flow_message
        WHERE
            doc_id          = @docid
            and Flow_ID     = @Flow_ID
            and Step_ID     = @Step_ID
            and staff_id    = @staffid
    
    --小于0为全体通过（全体指已经发送签收的人员）
    IF(     SELECT COUNT(*) FROM dbo.UDS_Flow_Status where Doc_id = @docid ) 
        <> (SELECT COUNT(*) FROM dbo.uds_flow_postil WHERE doc_id =@docid and postil_type = /* approved */1 AND Step_ID = @Step_ID)
        RETURN -4

END
ELSE
BEGIN
    --需要汇签个数的
    IF @PassNum > 0
    BEGIN
        --删除真正的消息
        DELETE FROM UDS_SMS_MobileMsgSendBuffer        
            WHERE     msgid in (
                        SELECT     msgid 
                            FROM 
                                dbo.UDS_Flow_Message
                            WHERE
                                doc_id          = @docid
                                and Flow_ID     = @Flow_ID
                                and Step_ID     = @Step_ID            
                                and staff_id    = @staffid        
                      )

        --删除当前步骤的提醒
        DELETE FROM dbo.uds_flow_message
            WHERE
                doc_id          = @docid
                and Flow_ID     = @Flow_ID
                and Step_ID     = @Step_ID
                and staff_id    = @staffid

        UPDATE dbo.UDS_Flow_Status SET status = 2 WHERE staff_id = @staffid and doc_id = @docid;

        --小于汇签人数则不继续
        IF (SELECT COUNT(*) FROM dbo.UDS_Flow_Postil WHERE doc_id =@docid and postil_type = /* approved */1 and Step_ID = @Step_ID) < @PassNum
            RETURN -5
    END
    --等于0为不需要汇签的
END

-- 删除上一步骤的签收标志
DELETE FROM dbo.uds_flow_status WHERE doc_id = @DocID;

-- 更新文档标志
UPDATE dbo.uds_flow_document SET doc_status = 0, isrunning = 1 WHERE doc_id = @DocID;

-- 根据条件判断，得到流转到的步骤ID
DECLARE @NextStepID INT;
exec dbo.sproc_Flow_GetPrePostInfo_V2
	@StaffName = @StaffName,
	@DocID = @DocID,
	@NextStepID = @NextStepID output,
	@NextFlowRule = @FlowRule output;

-- 对于不是最后步骤的
IF @NextStepID > 0
BEGIN
    CREATE TABLE #staff (id int)
    
    IF @FlowRule = 0		/* 按绑定的人员流转 */
    BEGIN
        INSERT INTO #staff
            SELECT staff_id 
                FROM dbo.v_uds_flow_staff_in_step
                WHERE Flow_ID = @Flow_ID AND Step_ID = @NextStepID
    END
    IF @FlowRule = 1		/* 按职位流转 */
    BEGIN
        --挑选上级人员
        WHILE NOT EXISTS(SELECT 1 FROM #staff)
        BEGIN
            --得到上级职位的所在成员在绑定组里成员
            INSERT INTO #staff
                SELECT	a.staff_id 
                    FROM 
                        dbo.UDS_Staff_In_Position a,
                        dbo.v_uds_flow_staff_in_step b
                    WHERE
						a.staff_id			= b.staff_id 
                        and a.Position_id	= @Super_Position_ID
                        and b.Flow_ID       = @Flow_ID         
                        and b.Step_ID       = @NextStepID

            IF @Position_ID <> @Super_Position_ID
            BEGIN
                SET @Position_ID = @Super_Position_ID;
                SELECT @Super_Position_ID = Super_Position_ID FROM dbo.UDS_Position WHERE Position_id = @Position_ID                ;
            END 
            ELSE
            BEGIN
                BREAK
            END 
        END
    END
    IF @FlowRule = 2	/* 按项目流转 */
    BEGIN
        --如果是本组组长，选上级
        IF EXISTS(SELECT 1 FROM dbo.uds_staff_in_team WHERE team_id = @Project_ID AND staff_id = @staffid and member_type = 2)
        BEGIN
            SET @Project_ID = @Parent_Project_ID
        END

        --挑选上级人员
        WHILE NOT EXISTS(SELECT 1 FROM #staff)
        BEGIN            
            --得到上级项目成员在绑定组中成员                

            INSERT INTO #staff
                SELECT     a.staff_id
                    FROM 
                        dbo.uds_staff_in_team a,
                        dbo.v_uds_flow_staff_in_step b
                    WHERE
						a.staff_id		  = b.staff_id 
                        and a.team_id     = @Project_ID
                        and b.Flow_ID     = @Flow_ID
                        and b.Step_ID     = @NextStepID
                        and a.member_type = 2

            IF @Project_ID <> @Parent_Project_ID
            BEGIN
                SET @Project_ID = @Parent_Project_ID;
                SELECT @Parent_Project_ID = ClassParentID FROM dbo.UDS_Class WHERE classid = @Project_ID;
            END 
            ELSE
            BEGIN    
                BREAK
            END

        END
    END

    
    --发送签收标志
    IF EXISTS(SELECT 1 FROM #staff)
    BEGIN
        --发送站内消息
        IF @Local_Alert = 1
        BEGIN
            INSERT INTO dbo.UDS_SMS_Msg (sender, content, sendtime, [type]) VALUES(@staffname, '有新公文需要审批！', getdate(), 1);
            SET @msgid = IDENT_CURRENT('uds_sms_msg');
            
            INSERT INTO dbo.UDS_SMS_Receiver (msgid, receiver, mobileno, [type], isread)
                SELECT @msgid, b.staff_name, b.mobile, 1, 0                        
                    FROM
						#staff a,
                        dbo.uds_staff b
                    WHERE
                        a.id = b.staff_id
        END

        --删除真正的消息
        DELETE FROM dbo.UDS_SMS_MobileMsgSendBuffer
            WHERE msgid in (
                        SELECT msgid FROM dbo.uds_flow_message WHERE doc_id = @docid
                      )

        --删除当前步骤的提醒
        DELETE FROM dbo.uds_flow_message WHERE doc_id = @docid;

        --发送站外手机短信
        DECLARE @StendTime DATETIME
        DECLARE @RepeatTimes int
        DECLARE @RepeatPeriod int
        
        SELECT  @StendTime = DATEADD(ss, basehour, getdate()), @RepeatTimes = Cyctimes -1, @RepeatPeriod = Period
            FROM 
                dbo.uds_flow_step
            WHERE 
                Flow_ID     = @Flow_ID
                and Step_ID     = @Step_ID
        IF @RepeatTimes>=0
        BEGIN
            DECLARE @staffids nvarchar(3000)
            SET     @staffids = ''
            SELECT     @staffids = @staffids + ',' + CONVERT(nvarchar(30),id)
                FROM #staff

            --发送流程提醒信息
            EXEC sproc_Flow_SMS_Send @docid,@Flow_ID,@NextStepID,@staffids,'有新催办公文需要审批！',@StendTime,@RepeatTimes,@RepeatPeriod
        END

        --填写文档发送到当前步骤的人员
        INSERT INTO dbo.UDS_Flow_Status
			SELECT @DocID, ID, /* 未签 */0
			FROM #staff

        --备份当前步骤
        INSERT INTO uds_flow_path (doc_id,Flow_ID,Step_ID,staff_id,order_id)
            SELECT     @DocID,@Flow_ID,@NextStepID,id,(SELECT COALESCE(MAX(order_id),0)+1 FROM uds_flow_path WHERE doc_id= @docid) AS order_id
                FROM 
                    #staff

        --更新文档状态
        UPDATE dbo.uds_flow_document 
            SET Step_ID		= @NextStepID,
                obj_id		= @projectId,
                obj_type    = @FlowRule
            WHERE
				doc_id		= @docid        
        RETURN 0
    END
    ELSE
    BEGIN        
        IF @FlowRule = 0
        BEGIN
            PRINT '步骤没有成员'        
            RETURN -7
        END
        IF @FlowRule = 1 
        BEGIN
            PRINT '没有职位上级'        
            RETURN -2
        END
        IF @FlowRule = 2 
        BEGIN
            PRINT '没有项目上级'        
            RETURN -3
        END
        
    END

END  /* IF @NextStepID > 0 */
ELSE
BEGIN
    /*
     * The flow was completed for this document.
     */

    --删除当前文档的真正提醒
    DELETE FROM dbo.UDS_SMS_MobileMsgSendBuffer
        WHERE msgid in (    
                SELECT msgid 
                        FROM 
                            uds_flow_message
                        WHERE     doc_id = @docid
                )

    --删除当前文档的提醒
    DELETE FROM dbo.uds_flow_message
        WHERE doc_id = @docid

    --给拟稿者发送消息
    INSERT INTO dbo.uds_sms_msg (sender, content, sendtime, [type]) VALUES (@StaffName, N'文档已经审批完成！', getdate(), 1);
    
    SET @msgid = @@IDENTITY;
    INSERT INTO dbo.uds_sms_receiver (msgid, receiver, mobileno, [type], isread)
        SELECT	@msgid, b.staff_name, b.mobile, 1, 0
            FROM 
                dbo.uds_flow_document a,
                dbo.uds_staff b                    
            WHERE     
                b.staff_id = a.doc_builder_id
                and a.doc_id = @docid

    --删除所有的状态
    Delete FROM dbo.uds_flow_status WHERE doc_id     = @DocID

    --设置标记
    UPDATE dbo.UDS_Flow_Document SET Doc_Status = 3 WHERE doc_id = @DocID
    PRINT '最后环节，完成文档！'
    RETURN -1
END		/* IF @NextStepID <= 0 */

END
GO
